-- 租户账号关联表建表语句
-- 对应实体类: com.finna.tenant.entity.TenantAccountJoin
-- 对应 resultMap: BaseResultMap

CREATE TABLE tenant_account_join (
    -- 主键
    id BIGSERIAL PRIMARY KEY,
    
    -- 租户ID，关联租户表
    tenant_id BIGINT NOT NULL,
    
    -- 账号ID，关联账号表
    account_id BIGINT NOT NULL,
    
    -- 角色ID，关联角色表
    role_id BIGINT,
    
    -- 角色名称
    role VARCHAR(50),
    
    -- 创建时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 更新时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 当前角色ID（用于角色切换）
    current_role_id BIGINT
);

-- 添加表注释
COMMENT ON TABLE tenant_account_join IS '租户账号关联表，管理租户与账号的多对多关系';

-- 添加字段注释
COMMENT ON COLUMN tenant_account_join.id IS '主键ID';
COMMENT ON COLUMN tenant_account_join.tenant_id IS '租户ID';
COMMENT ON COLUMN tenant_account_join.account_id IS '账号ID';
COMMENT ON COLUMN tenant_account_join.role_id IS '角色ID';
COMMENT ON COLUMN tenant_account_join.role IS '角色名称';
COMMENT ON COLUMN tenant_account_join.created_at IS '创建时间';
COMMENT ON COLUMN tenant_account_join.updated_at IS '更新时间';
COMMENT ON COLUMN tenant_account_join.current_role_id IS '当前角色ID';

-- 创建索引
-- 1. 租户ID索引（查询某个租户下的所有账号）
CREATE INDEX idx_tenant_account_join_tenant_id ON tenant_account_join(tenant_id);

-- 2. 账号ID索引（查询某个账号属于哪些租户）
CREATE INDEX idx_tenant_account_join_account_id ON tenant_account_join(account_id);

-- 3. 角色ID索引（查询某个角色下的所有关联）
CREATE INDEX idx_tenant_account_join_role_id ON tenant_account_join(role_id);

-- 4. 当前角色ID索引（查询当前角色）
CREATE INDEX idx_tenant_account_join_current_role_id ON tenant_account_join(current_role_id);

-- 5. 复合索引：租户+账号（查询特定租户下的特定账号）
CREATE UNIQUE INDEX idx_tenant_account_join_tenant_account ON tenant_account_join(tenant_id, account_id);

-- 6. 创建时间索引（按时间查询）
CREATE INDEX idx_tenant_account_join_created_at ON tenant_account_join(created_at);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_tenant_account_join_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_tenant_account_join_updated_at
    BEFORE UPDATE ON tenant_account_join
    FOR EACH ROW
    EXECUTE FUNCTION update_tenant_account_join_updated_at();

-- 插入示例数据（可选）
-- INSERT INTO tenant_account_join (tenant_id, account_id, role_id, role, current_role_id) VALUES
-- (1, 1, 1, 'admin', 1),
-- (1, 2, 2, 'user', 2),
-- (2, 1, 3, 'manager', 3);
